﻿'Description:接口日志管理类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2017-5-08
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports DatabaseAccess.DBUtility
Imports BusinessRule.DTO
Imports System.Data.OracleClient

Public Class uLMSInterfaceLoginBusiness
    Implements uLMSInterfaceLoginIBusiness
    ''' <summary>
    ''' 获取查询数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetInterfaceLoginData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSInterfaceLoginIBusiness.GetInterfaceLoginData
        Dim strSql As String = ""
        If para("Type") = "0" Then '生产准备
            strSql = "SELECT m.mfgordername,c.containername,ws.workflowstepname,pb.productname, " &
                   " '生产准备' recordtype,wte.recorddate,DECODE( wte.issuccess,'0','是','否') isok,wte.issuccess,nvl(wte.recordstatus,wte.issuccess) recordstatus,wte.recordid," &
                   " DECODE(wte.issuccess,'0',null,'<a href=''#'' onclick=DelRecord('||''''||wte.recordid||''''||''||','||ROWNUM||') style=''font-size: x-small;height:10px;text-align: center;color:Blue;''>删除</a>' ) delurl FROM Writetoerprecord wte " &
                   " INNER JOIN arrangements a ON a.arrangementsid=wte.recordid " &
                   " LEFT JOIN container c ON c.containerid=a.containerid " &
                   " LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid " &
                   " LEFT JOIN workflowstep ws ON ws.workflowstepid=a.workflowstepid " &
                   " LEFT JOIN product p ON p.productid=c.productid " &
                   " LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " &
                   " WHERE wte.recordtype='arrangement'	"
        ElseIf para("Type") = "1" Then '领料批次
            strSql = " SELECT m.mfgordername,c.containername,null workflowstepname,pb.productname," &
                    " '领料批次' recordtype,wte.recorddate,DECODE( wte.issuccess,'0','是','否') isok,wte.issuccess,nvl(wte.recordstatus,wte.issuccess) recordstatus,wte.recordid," &
                    " DECODE(wte.issuccess,'0',null,'<a href=''#'' onclick=DelRecord('||''''||wte.recordid||''''||''||','||ROWNUM||') style=''font-size: x-small;height:10px;text-align: center;color:Blue;''>删除</a>' ) delurl FROM writetoerprecord wte" &
                    " LEFT JOIN container c ON c.containerid=wte.recordid" &
                    " LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid" &
                    " LEFT JOIN product p ON p.productid=c.productid" &
                    " LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid" &
                    " WHERE wte.recordtype='container'"
        ElseIf para("Type") = "2" Then '生产报工
            strSql = <string>SELECT c.mfgordername,c.containername, ws.workflowstepname ,'报工' recordtype,c.createdate recorddate, 
                    c.containerstep,c.reportqty,c.lossqty,c.specno,c.sonspecno,DECODE(c.isuse,1,'撤销') revert
                    FROM mes_workingreport_table c 
                    LEFT JOIN Workflowstep ws ON ws.workflowstepid=SUBSTR(c.containerstep,17,16)</string>.Value
        ElseIf para("Type") = "3" Then '入库申请
            strSql = " SELECT cis.aufnr mfgordername,cis.charg containername,null workflowstepname,cis.matnr productname," &
            " '入库申请' recordtype,wte.recorddate,DECODE( wte.issuccess,'0','是','否') isok,wte.issuccess,nvl(wte.recordstatus,wte.issuccess) recordstatus," &
            " wte.recordid,DECODE(wte.issuccess,'0',null,'<a href=''#'' onclick=DelRecord('||''''||wte.recordid||''''||''||','||ROWNUM||') style=''font-size: x-small;height:10px;text-align: center;color:Blue;''>删除</a>' ) delurl  FROM writetoerprecord wte" &
            " LEFT JOIN containerinstore cis ON cis.containerinstoreid=wte.recordid" &
            " LEFT JOIN container c ON c.containername=cis.charg" &
            " LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid" &
            " WHERE wte.recordtype='instore'"
        ElseIf para("Type") = "4" Then '不合格审理
            strSql = " SELECT cno.fertaufnr mfgordername,c.containername,cno.zmesgxtxt workflowstepname,cno.matnr productname," &
                    " '不合格审理' recordtype,wte.recorddate,DECODE( wte.issuccess,'0','是','否') isok,wte.issuccess,nvl(wte.recordstatus,wte.issuccess) recordstatus," &
                    " wte.recordid,DECODE(wte.issuccess,'0',null,'<a href=''#'' onclick=DelRecord('||''''||wte.recordid||''''||''||','||ROWNUM||') style=''font-size: x-small;height:10px;text-align: center;color:Blue;''>删除</a>' ) delurl FROM Writetoerprecord wte" &
            " LEFT JOIN CONTAINERNONCONFORMINGINFO cno ON cno.containernonconforminginfoid=wte.recordid" &
            " LEFT JOIN container c ON c.containerid=cno.containerid" &
            " LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid" &
            " WHERE wte.recordtype='nonconforming'"
        ElseIf para("Type") = "6" Then '派工信息
            strSql = "SELECT wte.recorddate,wte.recordid,'派工' recordtype,wte.issuccess,nvl(wte.recordstatus,wte.issuccess) recordstatus,DECODE( wte.issuccess,'0','是','否') isok,DECODE(wte.issuccess,'0',null,'<a href=''#'' onclick=DelRecord('||''''||wte.recordid||''''||''||','||ROWNUM||') style=''font-size: x-small;height:10px;text-align: center;color:Blue;''>删除</a>' ) delurl,m.mfgordername,mst.batchno containername,pb.productname,mst.stepname workflowstepname,e.fullname disEmploeeName,DECODE(mst.status,'0','派工添加','1','派工删除','2','派工修改') DispatchType" &
                    " FROM Writetoerprecord wte" &
                    " LEFT JOIN mes_senddispatch_table mst ON mst.taskno=wte.recordid" &
                    " LEFT JOIN container c ON c.containername=mst.batchno" &
                    " LEFT JOIN product p ON p.productid=c.productid " &
                   " LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " &
                    " LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid" &
                    " LEFT JOIN employee e ON e.employeename=mst.welderno"
            strSql += " WHERE mst.FACTORYID='" & para("FactoryID") & "'"
        ElseIf para("Type") = "7" Then '给ERP工艺信息
            strSql = <string>select cci.containername,cci.mfgordername,wb.workflowname||':'||w.workflowrevision workflowinfo,wt.issuccess,
wt.resxml recordstatus,wt.createdate recorddate,wt.parentid RecordID,cci.workflowstepname from containercurrentinfo cci

left join sendtoerpworkflowinfo sw on sw.containerid=cci.containerid

INNER JOIN writetoothersystem wt on wt.parentid=sw.sendtoerpworkflowinfoid and wt.recordtype='SendToErpWorkflowInfo'

left join workflow w on w.workflowid=sw.workflowid

left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid

where 1=1 </string>.Value
        End If
        strSql = " select *  from (" & strSql & ") t where 1=1"
        strSql += GetCondition(para)
        strSql += " ORDER BY t.containername asc,t.workflowstepname asc,t.recorddate DESC"
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql
        GetInterfaceLoginData = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 增加条件
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCondition(ByVal para As Dictionary(Of String, String)) As String
        Dim strCondition As String = ""
        If para.ContainsKey("RecordStartDate") Then
            strCondition += String.Format(" AND to_date(SUBSTR(to_char(t.recorddate,'yyyy-mm-dd'),1,10),'yyyy-mm-dd')>=to_date('{0}','yyyy-mm-dd')", para("RecordStartDate"))
        End If
        If para.ContainsKey("RecordEndDate") Then
            strCondition += String.Format(" AND to_date(SUBSTR(to_char(t.recorddate,'yyyy-mm-dd'),1,10),'yyyy-mm-dd')<=to_date('{0}','yyyy-mm-dd')", para("RecordEndDate"))
        End If
        If para.ContainsKey("IsSuccess") Then
            If para("IsSuccess") = "0" Then
                strCondition += " AND t.issuccess='0' "
            Else
                strCondition += " AND (t.issuccess<>'0') "
            End If
        End If
        If para.ContainsKey("MfgOrderName") Then
            strCondition += String.Format(" AND t.MfgOrderName like '%{0}%'", para("MfgOrderName"))
        End If
        If para.ContainsKey("ContainerName") Then
            strCondition += String.Format(" AND t.ContainerName like '%{0}%'", para("ContainerName"))
        End If
        If para.ContainsKey("ProductName") Then
            strCondition += String.Format(" AND t.ContainerName like '%{0}%'", para("ProductName"))
        End If
        GetCondition = strCondition
    End Function
    ''' <summary>
    ''' 按唯一ID删除日志记录
    ''' </summary>
    ''' <param name="ID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DelWriteToErp(ByVal ID As String) As Boolean Implements uLMSInterfaceLoginIBusiness.DelWriteToErp
        Dim strSql As String = " DELETE Writetoerprecord WHERE recordid=:recordid"

        Dim deletePara(0) As OracleParameter
        deletePara(0) = DatabaseHelper.NewParameter("recordid", ID, OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, deletePara)
        If i > 0 Then
            DelWriteToErp = True
        Else
            DelWriteToErp = False
        End If
    End Function
    ''' <summary>
    ''' 获取PDM推送工艺日志
    ''' add:Wangjh 20180417
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetPDMToMESLog(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSInterfaceLoginIBusiness.GetPDMToMESLog
        Dim strSql As String = "SELECT * FROM interface_log  il WHERE il.logtype like '%PDMTOMES_%'"
        If para.ContainsKey("Product") Then
            strSql += String.Format(" and il.product LIKE '%{0}%'", para("Product"))
        End If
        If para.ContainsKey("RecordStartDate") Then
            strSql += String.Format(" AND to_date(SUBSTR(to_char(wte.recorddate,'yyyy-mm-dd'),1,10),'yyyy-mm-dd')>=to_date('{0}','yyyy-mm-dd')", para("RecordStartDate"))
        End If
        If para.ContainsKey("RecordEndDate") Then
            strSql += String.Format(" AND to_date(SUBSTR(to_char(wte.recorddate,'yyyy-mm-dd'),1,10),'yyyy-mm-dd')<=to_date('{0}','yyyy-mm-dd')", para("RecordEndDate"))
        End If
        If para.ContainsKey("IsSuccess") Then
            If para("IsSuccess") = "0" Then '成功
                strSql += String.Format(" AND IL.LOGMESSAGE LIKE '%成功%'")
            ElseIf para("IsSuccess") = "1" Then
                strSql += String.Format(" AND IL.LOGMESSAGE not LIKE '%成功%'")
            End If
        End If
        strSql += " ORDER BY il.createdate DESC"
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql
        GetPDMToMESLog = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
End Class
